#!/usr/bin/env perl

# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.

################################################################################

$cfg = {
  'driver' => 'Hive',
  'groups' => [ {
      'name' => 'Checkin',
      'tests' => [ {
          'num' => 1,
          'sql' => q\select * from studenttab10k;\,
          'floatpostprocess' => 1,
          'delimiter' => '	',
        }, {
          'num' => 2,
          'sql' => q\select registration, sum(contributions) s
                     from studenttab10k s join votertab10k v
                         on (s.name = v.name and s.age = v.age)
                     where s.age < 50 and v.age < 50
                     group by registration
                     order by s\,
          'sortArgs' => ['-t', '	', '-k', '2,2'],
          'floatpostprocess' => 1,
          'delimiter' => '	',
        }, {
          'num' => 3,
          'sql' => q\drop table if exists checkin_3;
                     create table checkin_3 as
                     select name, count(1)
                     from studenttab10k 
                     group by name
                     having count(1) > 5\,
          'result_table' => 'checkin_3',
          'verify_sql' =>q\select name, count(1)
                     from studenttab10k 
                     group by name
                     having count(1) > 5\,
        }, {
          'num' => 4,
          'sql' => q\select avg(gpa) average
                     from studentparttab30k
                     where age > 50\,
          'floatpostprocess' => 1,
          'delimiter' => '	',
        }
      ]
    },{
      'name' => 'SelectExpression',
      'tests' => [ {
          'num' => 1,
          'sql' => "select t + 10, t - 10, t * 10, t / 10, t % 10
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 2,
          'sql' => "select t + 10.10, t - 10.10, t * 10.10, t / 10.0
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 3,
          'sql' => "select si + 10, si - 10, si * 10, si / 10, si % 10
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 4,
          'sql' => "select si + 10.10, si - 10.10, si * 10.10, si / 10.0
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 5,
          'sql' => "select i + 10, i - 10, i * -1, i / 10, i % 10
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 6,
          'sql' => "select i + 10.10, i - 10.10, i * 10.10, i / 10.0
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{ # division removed because I can't get hive and mysql to do their
          # floating point arithmetic in the same way.
          'num' => 7,
          'sql' => "select b + 10, b - 10, b * -1, b % 10
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
#     },{ has floating point precision issues
#         'num' => 8,
#         'sql' => "select b + 10.10, b - 10.10, b * 10.10, b / 10.0
#                   from all100k;",
#         'floatpostprocess' => 1,
#         'delimiter' => '	',
      },{
          'ignore' => 1, # Has floating point issues
          'num' => 9,
          'sql' => "select f + 10, f - 10, f * 1.01, f / 10
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'ignore' => 1, # has floating point precision issues
          'num' => 10,
          'sql' => "select f + 10.10, f - 10.10, f * 10.10, f / 10.0
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 11,
          'sql' => "select d + 10, d - 10, d * 10, d / 10
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 12,
          'sql' => "select d + 10.10, d - 10.10, d * 1.01, d / 10.0
                    from all100k;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },
    ]
  },{
      'name' => 'WhereExpression',
      'tests' => [ {
          'num' => 1,
          'sql' => "select t
                    from all100k
                    where t = -91 and si = -19299 and i = -1591211872 
                        and b = -4485904205832126464 
                        and s = 'katie young';",
      },{
          'num' => 2,
          'sql' => "select t
                    from all100k
                    where f > 48308 and f < 48309;"
      },{
          'num' => 3,
          'sql' => "select t
                    from all100k
                    where d = -2806609.87;",
      },{
          'num' => 4,
          'sql' => "select t
                    from all100k
                    where t = 87 or si = 4931;",
      },{
          'num' => 5,
          'sql' => "select t
                    from all100k
                    where i <> 1096589477;",
      },{
          'num' => 6,
          'sql' => "select t
                    from all100k
                    where t > 0 and si > 0 and i > 0 and b > 0 and f > 0.0 and
                    d > 0.0 and s > 'm';",
      },{
          'num' => 7,
          'sql' => "select t
                    from all100k
                    where t >= 0 and si >= 0 and i >= 0 and b >= 0 and f >= 0.0
                    and d >= 0.0 and s >= 'm';",
      },{
          'num' => 8,
          'sql' => "select t
                    from all100k
                    where t < 0 and si < 0 and i < 0 and b < 0 and f < 0.0 and
                    d < 0.0 and s < 'm';",
      },{
          'num' => 9,
          'sql' => "select t
                    from all100k
                    where t <= 0 and si <= 0 and i <= 0 and b <= 0 and f <= 0.0
                    and d <= 0.0 and s <= 'm';",
      },{
          'num' => 10,
          'sql' => "select name
                    from studentnull10k
                    where age is null;",
          'nullpostprocess' => 1,
      },{
          'num' => 11,
          'sql' => "select name
                    from studentnull10k
                    where age is not null;",
          'nullpostprocess' => 1,
      },{
          'num' => 12,
          'sql' => "select age
                    from studenttab10k
                    where name like '_uke king';",
      },{
          'num' => 13,
          'sql' => "select age
                    from studenttab10k
                    where name like '% king';",
      },{
          'num' => 14,
          'sql' => "select age
                    from studenttab10k
                    where name regexp '.* king';",
      },{
          'num' => 15,
          'sql' => "select age
                    from studenttab10k
                    where name rlike '.* king';",
          'verify_sql' =>"select age
                    from studenttab10k
                    where name regexp '.* king';",
      }
    ]
  },{
      'name' => 'WherePartition',
      'tests' => [ {
          'num' => 1,
          'sql' => "select name
                    from studentparttab30k
                    where ds = '20110924';",
      },{
          'num' => 2,
          'sql' => "select name
                    from studentparttab30k
                    where ds = '20110924' or ds = '20110925';",
      },{
          'num' => 3,
          'sql' => "select name
                    from studentparttab30k
                    where ds > '20110924';",
      },{
          'num' => 4,
          'sql' => "select name
                    from studentparttab30k
                    where ds >= '20110924';",
      },{
          'num' => 5,
          'sql' => "select name
                    from studentparttab30k
                    where ds <= '20110926';",
      },{
          'num' => 6,
          'sql' => "select name
                    from studentparttab30k
                    where ds < '20110926';",
      },{
          'num' => 7,
          'sql' => "select name
                    from studentparttab30k
                    where ds <> '20110926';",
      }
    ]
  },{
      'name' => 'Distinct',
      'tests' => [ {
          'num' => 1,
          'sql' => "select distinct name
                    from studenttab10k;",
      },{
          'num' => 2,
          'sql' => "select distinct name, age
                    from studenttab10k;",
      }
    ]
  },{
      'name' => 'All',
      'tests' => [ {
          'num' => 1,
          'sql' => "select all name
                    from studenttab10k;",
      }
    ]
  },{
      'name' => 'Join',
      'tests' => [ {
          'num' => 1,
          'sql' => "select registration
                    from studenttab10k s join votertab10k v
                        on (s.name = v.name);",
      },{
          'num' => 2,
          'sql' => "select registration
                    from studenttab10k s join votertab10k v
                        on (s.name = v.name and s.age = v.age);",
      },{
          'num' => 3,
          'sql' => "select registration
                    from studenttab10k s join votertab10k v
                        on (s.name = v.name) join studentparttab30k p
                        on (p.name = v.name)
                    where s.age < 25 and v.age < 25 and p.age < 25;",
      },{
          'num' => 4,
          'sql' => "select registration
                    from studenttab10k s left outer join votertab10k v
                        on (s.name = v.name);",
          'nullpostprocess' => 1,
      },{
          'num' => 5,
          'sql' => "select registration
                    from studenttab10k s right outer join votertab10k v
                        on (s.name = v.name);",
          'nullpostprocess' => 1,
#     },{
#         'num' => 6,
#         'sql' => "select registration
#                   from studenttab10k s full outer join votertab10k v
#                       on (s.name = v.name);",
#         'nullpostprocess' => 1,
#         'verify_sql' => "select registration
#                          from studenttab10k s left join votertab10k v
#                              on (s.name = v.name)
#                          union all
#                          select registration
#                          from votertab10k v left join studenttab10k s
#                              on (s.name = v.name);",
      },{
          'num' => 7,
          'sql' => "select registration
                    from studenttab10k s join votertab10k v
                    where s.age < 25 and v.age < 25;",
      }
    ]
  },{
      'name' => 'GroupBy',
      'tests' => [ {
          'num' => 1,
          'sql' => "select count(*)
                    from studentparttab30k;",
      },{
          'num' => 2,
          'sql' => "select name, count(*)
                    from studenttab10k
                    group by name;",
      },{
          'num' => 3,
          'sql' => "select name, avg(age)
                    from studentparttab30k
                    group by name;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 4,
          'sql' => "select name, sum(contributions)
                    from votertab10k 
                    group by name;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 5,
          'sql' => "select name, age, max(contributions)
                    from votertab10k 
                    where registration = 'democrat'
                    group by name, age;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 6,
          'sql' => "select name, min(contributions)
                    from votertab10k 
                    where registration = 'green'
                    group by name;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 7,
          'sql' => "select name, min(age)
                    from votertab10k 
                    group by name;",
      },{
          'num' => 8,
          'sql' => "select name, max(age)
                    from votertab10k 
                    group by name;",
      },{
          'num' => 9,
          'sql' => "select age, max(name)
                    from votertab10k 
                    group by age;",
      },{
          'num' => 10,
          'sql' => "select age, min(name)
                    from votertab10k 
                    group by age;",
      },{
          'num' => 11,
          'sql' => "select registration, sum(contributions)
                    from studenttab10k s join votertab10k v
                        on (s.name = v.name)
                    group by registration;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      }
    ]
  },{
      'name' => 'GroupByDistinct',
      'tests' => [ {
          'num' => 1,
          'sql' => "select name, count(distinct registration)
                    from votertab10k
                    group by name;",
        },{
          'num' => 2,
          'sql' => "select name, count(distinct registration), count(age)
                    from votertab10k
                    group by name;",
      },{
          'num' => 3,
          'sql' => "select name, count(distinct registration), count(distinct age)
                    from votertab10k
                    group by name;",
      },{
          'num' => 4,
          'sql' => "select s.name, count(distinct registration)
                    from studenttab10k s join votertab10k v
                        on (s.name = v.name)
                    group by s.name;",
      }
    ]
  },{
      'name' => 'Having',
      'tests' => [ {
          'num' => 1,
          'sql' => "select name, sum(age)
                    from votertab10k
                    group by name
                    having sum(age) > 1000;",
      },{
          'num' => 2,
          'sql' => "select age
                    from votertab10k
                    group by age
                    having sum(age) > 1000;",
      },{
          'num' => 3,
          'sql' => "select age, count(distinct name)
                    from votertab10k
                    group by age
                    having count(distinct name) > 50;",
      },{
          'num' => 4,
          'sql' => "select registration, sum(contributions)
                    from studenttab10k s join votertab10k v
                        on (s.name = v.name)
                    group by registration
                    having sum(contributions) > 100.0;",
          'floatpostprocess' => 1,
          'delimiter' => '	',
      }
    ]
  },{
      'name' => 'OrderBy',
      'tests' => [ {
          'num' => 1,
          'sql' => "select name
                    from studenttab10k
                    order by name;",
          'sortArgs' => ['-t', '	', '-k', '1,1'],
      },{
          'num' => 2,
          'sql' => "select age
                    from studenttab10k
                    order by age;",
          'sortArgs' => ['-t', '	', '-k', '1n,1n'],
      },{
          'num' => 3,
          'sql' => "select gpa
                    from studenttab10k
                    order by gpa;",
          'sortArgs' => ['-t', '	', '-k', '1n,1n'],
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 4,
          'sql' => "select age
                    from studentnull10k
                    order by age;",
          'sortArgs' => ['-t', '	', '-k', '1n,1n'],
      },{
          'num' => 5,
          'sql' => "select t
                    from all100k
                    order by t;",
          'sortArgs' => ['-t', '	', '-k', '1n,1n'],
      },{
          'num' => 6,
          'sql' => "select si
                    from all100k
                    order by si;",
          'sortArgs' => ['-t', '	', '-k', '1n,1n'],
      },{
          'num' => 7,
          'sql' => "select b
                    from all100k
                    order by b;",
          'sortArgs' => ['-t', '	', '-k', '1n,1n'],
      },{
#         'num' => 8,  bools not loaded in mysql correctly
#         'sql' => "select bool
#                   from all100k
#                   order by bool;",
#     },{
          'num' => 9,
          'sql' => "select d
                    from all100k
                    order by d;",
          'sortArgs' => ['-t', '	', '-k', '1n,1n'],
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 10,
          'sql' => "select name, count(*) cnt
                    from studenttab10k
                    group by name
                    order by cnt;",
          'sortArgs' => ['-t', '	', '-k', '2n,2n'],
      },{
          'num' => 11,
          'sql' => "select name, age
                    from studenttab10k
                    order by name, age;",
          'sortArgs' => ['-t', '	', '-k', '1,1', '-k', '2n,2n'],
      },{
          'num' => 12,
          'sql' => "select name
                    from studenttab10k
                    order by name desc;",
          'sortArgs' => ['-t', '	', '-r', '-k', '1,1'],
      },{
          'num' => 13,
          'sql' => "select age
                    from studenttab10k
                    order by age desc;",
          'sortArgs' => ['-t', '	', '-k', '1rn,1rn'],
      },{
          'num' => 14,
          'sql' => "select gpa
                    from studenttab10k
                    order by gpa desc;",
          'sortArgs' => ['-t', '	', '-k', '1rn,1rn'],
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 15,
          'sql' => "select age
                    from studentnull10k
                    order by age desc;",
          'sortArgs' => ['-t', '	', '-k', '1rn,1rn'],
      },{
          'num' => 16,
          'sql' => "select t
                    from all100k
                    order by t desc;",
          'sortArgs' => ['-t', '	', '-k', '1rn,1rn'],
      },{
          'num' => 17,
          'sql' => "select si
                    from all100k
                    order by si desc;",
          'sortArgs' => ['-t', '	', '-k', '1rn,1rn'],
      },{
          'num' => 18,
          'sql' => "select b
                    from all100k
                    order by b desc;",
          'sortArgs' => ['-t', '	', '-k', '1rn,1rn'],
      },{
#         'num' => 19, bools not loaded into mysql correctly
#         'sql' => "select bool
#                   from all100k
#                   order by bool desc;",
#     },{
          'num' => 20,
          'sql' => "select d
                    from all100k
                    order by d desc;",
          'sortArgs' => ['-t', '	', '-k', '1rn,1rn'],
          'floatpostprocess' => 1,
          'delimiter' => '	',
      },{
          'num' => 21,
          'sql' => "select name, age
                    from studenttab10k
                    order by name, age desc;",
          'sortArgs' => ['-t', '	', '-k', '1,1', '-k', '2nr,2nr'],
      },{
          'num' => 22,
          'sql' => "select name, age
                    from studenttab10k
                    order by name desc, age;",
          'sortArgs' => ['-t', '	', '-k', '1r,1r', '-k', '2n,2n'],
      },{
          'num' => 23,
          'sql' => "select name, age
                    from studenttab10k
                    order by name desc, age desc;",
          'sortArgs' => ['-t', '	', '-k', '1r,1r', '-k', '2rn,2rn'],
      },{
          'num' => 24,
          'sql' => "select registration, s.name
                    from studenttab10k s join votertab10k v
                        on (s.name = v.name)
                    order by s.name;",
          'sortArgs' => ['-t', '	', '-k', '2,2'],
      }
    ]
  },{
      'name' => 'Insert',
      'tests' => [ {
          'num' => 1, # insert map only
          'sql' => "drop table if exists insert_1;
                    create table insert_1 (
                       name string,
                       age int)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;
                    insert overwrite table insert_1
                    select name, age
                    from studenttab10k
                    where age > 50;",
          'result_table' => 'insert_1',
          'verify_sql' =>"select name, age
                    from studenttab10k
                    where age > 50;",
        },{ 
          'num' => 2, # insert reduce side
          'sql' => "drop table if exists insert_2;
                    create table insert_2 (
                       name string,
                       avgage double)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;
                    insert overwrite table insert_2
                    select name, avg(age) as avgage
                    from studenttab10k
                    group by name;",
          'result_table' => 'insert_2',
          'floatpostprocess' => 1,
          'delimiter' => '	',
          'verify_sql' =>"select name, avg(age)
                    from studenttab10k
                    group by name;",
#       },{ # Commented out until we switch to Hive 0.8
#         'num' => 3, # insert map only overwrite
#         'sql' => "create table if not exists insert_3 (
#                      name string,
#                      age int)
#                   row format delimited
#                   fields terminated by '\\t'
#                   stored as textfile;
#                   insert into table insert_3
#                   select name, age
#                   from studenttab10k
#                   where age > 50;
#                   insert overwrite table insert_3
#                   select name, age
#                   from studenttab10k
#                   where age > 30;",
#         'result_table' => 'insert_3',
#         'verify_sql' =>"select name, age
#                   from studenttab10k
#                   where age > 30;",
#       },{# Commented out until we switch to Hive 0.8
#         'num' => 4, # insert reduce side overwrite
#         'sql' => "create table if not exists insert_4 (
#                      name string,
#                      age double)
#                   row format delimited
#                   fields terminated by '\\t'
#                   stored as textfile;
#                   insert into table insert_4
#                   select name, avg(age) as avgage
#                   from studenttab10k
#                   group by name;
#                   insert overwrite table insert_4
#                   select name, avg(contributions)
#                   from votertab10k
#                   group by name;",
#         'result_table' => 'insert_4',
#         'verify_sql' =>"select name, avg(contributions)
#                         from votertab10k
#                         group by name;",
        },{
          'num' => 5, # insert partition 
          'sql' => "drop table if exists insert_5;
                    create table insert_5 (
                       name string,
                       age int)
                    partitioned by (ds string)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;
                    insert overwrite table insert_5 partition (ds='20110924')
                    select name, age
                    from studentparttab30k
                    where ds = '20110924'
                    order by name;",
          'result_table' => 'insert_5',
          'verify_sql' =>"select name, age, ds
                    from studentparttab30k
                    where ds = '20110924';",
#       },{# Commented out until we switch to Hive 0.8
#         'num' => 6, # insert partition overwrite
#         'sql' => "create table if not exists insert_6 (
#                      name string,
#                      age int)
#                   partitioned by (ds string)
#                   row format delimited
#                   fields terminated by '\\t'
#                   stored as textfile;
#                   insert into table insert_6 partition (ds='20110925')
#                   select name, age
#                   from studenttab10k
#                   order by name;
#                   insert overwrite table insert_6 partition (ds='20110925')
#                   select name, age
#                   from studentparttab30k
#                   where ds = '20110925'
#                   order by name;",
#         'result_table' => 'insert_6',
#         'verify_sql' =>"select name, age, ds
#                         from studentparttab30k
#                         where ds = '20110925';",
        },{
          'num' => 7, # insert multiple partitions
          'sql' => "drop table if exists insert_7;
                    create table insert_7 (
                       name string,
                       age int)
                    partitioned by (ds string)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;
                    insert overwrite table insert_7 partition (ds)
                    select name, age, ds
                    from studentparttab30k
                    order by name;",
          'result_table' => 'insert_7',
          'verify_sql' =>"select name, age, ds
                          from studentparttab30k;",
          'hivecmdargs' => [ "--hiveconf",
                             "hive.exec.dynamic.partition.mode=nonstrict",
                             "--hiveconf",
                             "hive.exec.dynamic.partition=true"],
        }
    ]
  },{
      'name' => 'MultiInsert',
      'tests' => [ {
          'num' => 1, # insert map only
          'sql' => "drop table if exists multi_insert_1_1;
                    drop table if exists multi_insert_1_2;
                    drop table if exists multi_insert_1_3;

                    create table multi_insert_1_1 (
                       name string,
                       ds string)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;

                    create table multi_insert_1_2 (
                       name string,
                       ds string)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;

                    create table multi_insert_1_3 (
                       name string,
                       ds string)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;

                    from studentparttab30k
                    insert overwrite table multi_insert_1_1
                    select name, ds
                    where ds = '20110924'

                    insert overwrite table multi_insert_1_2
                    select name, ds
                    where ds = '20110925'

                    insert overwrite table multi_insert_1_3
                    select name, ds
                    where ds = '20110926';",
          'result_table' => ['multi_insert_1_1',
                             'multi_insert_1_2',
                             'multi_insert_1_3'],
          'verify_sql' =>["select name, ds
                           from studentparttab30k
                           where ds = '20110924';",
                          "select name, ds
                           from studentparttab30k
                           where ds = '20110925';",
                          "select name, ds
                           from studentparttab30k
                           where ds = '20110926';"]
        },{ 
          'num' => 2, # insert reduce side
          'sql' => "drop table if exists multi_insert_2_1;
                    drop table if exists multi_insert_2_2;
                    drop table if exists multi_insert_2_3;

                    create table multi_insert_2_1 (
                       name string,
                       avgage double)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;

                    create table multi_insert_2_2 (
                       name string,
                       age  int,
                       sumgpa double)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;

                    create table multi_insert_2_3 (
                       name string,
                       distage bigint)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;

                    from studenttab10k
                    insert overwrite table multi_insert_2_1
                    select name, avg(age) as avgage
                    group by name
                    
                    insert overwrite table multi_insert_2_2
                    select name, age, sum(gpa) as sumgpa
                    group by name, age

                    insert overwrite table multi_insert_2_3
                    select name, count(distinct age) as distage
                    group by name;
                    ",
          'result_table' => ['multi_insert_2_1',
                             'multi_insert_2_2',
                             'multi_insert_2_3'],
          'floatpostprocess' => 1,
          'delimiter' => '	',
          'verify_sql' =>["select name, avg(age)
                           from studenttab10k
                           group by name;",
                          "select name, age, sum(gpa)
                           from studenttab10k
                           group by name, age;",
                          "select name, count(distinct age)
                           from studenttab10k
                           group by name;"],
        },{
          'num' => 3, # partition
          'sql' => "drop table if exists multi_insert_3;

                    create table multi_insert_3 (
                       name string)
                    partitioned by (ds string)
                    row format delimited
                    fields terminated by '\\t'
                    stored as textfile;

                    from studentparttab30k
                    insert overwrite table multi_insert_3
                        partition (ds = '20110924')
                    select name
                    where ds = '20110924'

                    insert overwrite table multi_insert_3
                        partition (ds = '20110925')
                    select name
                    where ds = '20110925'

                    insert overwrite table multi_insert_3
                        partition (ds = '20110926')
                    select name
                    where ds = '20110926';",
          'result_table' => 'multi_insert_3',
          'verify_sql' =>"select name, ds
                          from studentparttab30k;",
        }
    ]
  },{
      'name' => 'Subquery',
      'tests' => [ {
          'num' => 1,
          'sql' => "select name, age
                    from (select name, age, gpa
                          from studenttab10k
                          union all
                          select name, age, gpa
                          from studentparttab30k
                          where ds = '20110924') t1
                    where age < 25;",
          'verify_sql' => "select name, age
                          from studenttab10k
                          where age < 25
                          union all
                          select name, age
                          from studentparttab30k
                          where ds = '20110924' and age < 25;"
        },
    ]
  },{
      'name' => 'Limit',
      'tests' => [ {
 	'num' => 1,
          'sql' => "select name
                    from studentparttab30k
                    where ds = '20110924'
                    limit 5 ;",
        },{
          'num' => 2, #This test fails. Need to investigate more
          'sql' => "select name,age
                    from studenttab10k
                    order by name desc, age limit 10; ",
      }
  ]
  },{ 
      'name' => 'SortBy',
      'tests' => [ {
          'num' => 1, 
          'sql' => "select name
                    from studenttab10k
                    sort by name;",
          'verify_sql' =>"select name
                    from studenttab10k
                    order by name;",
        }
    ]
  },{ 
      'name' => 'SelectRegex',
      'tests' => [ {
         'num' => 1,
          'sql' => "select `a[g]+e`
                from studenttab10k
                order by age;",        
          'verify_sql' => "select age
                    from studenttab10k
                    order by age;", 
        },{ 
          'num' => 2,
          'sql' => "select `n.*` 
         	from studenttab10k
                order by name;",	
          'verify_sql' => "select name
                    from studenttab10k
                    order by name;", 
        },{ 
          'num' => 3,
          'sql' => "select `(n|a)+.+`
                from studenttab10k
                order by name;",        
          'verify_sql' => "select name, age
                    from studenttab10k
                    order by name;", 
        },{
          'num' => 4,
          'sql' => "select `[l-o]+.+`
                from studenttab10k
                order by name;",        
          'verify_sql' => "select name
                    from studenttab10k
                    order by name;", 
        },{
          'num' => 5,
          'sql' => "select `(n|a)?.+e`
                from studenttab10k
                order by name;",        
          'verify_sql' => "select name,age
                    from studenttab10k
                    order by name;", 
      }
    ]
 } 
      # Need to test multiple insert  - Need harness enhancements
      # Need to test insert into directory - Need harness enhancements
      # Need to test casts
      # Need to test all built in expressions and UDF (see https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF)
      # Need to test xpath functionality
      # Need to test regular expression based projection
      # Need to test semi joins - Mysql doesn't support, how do I express semi-join?
      # Need to test map side group by
      # Need to test limit
      # Need to test sort by
      # Need to test distribute by
      # Need to test cluster by
      # Need to test transforms
      # Need to test lateral transforms
      # Need to test subqueries
  ],
},
;



